﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ModelLayer;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace CinemaDbLib
{
	public class TicketDB
	{
		private Connection connection;
		private ScheduleDB scheduleDB;
		private SeatDB seatDB;

		public TicketDB()
		{
			connection = new Connection();
			scheduleDB = new ScheduleDB();
			seatDB = new SeatDB();
		}

		public List<Ticket> getTickets()
		{
			return where("");
		}

		public Ticket getTicketById(int id)
		{
			return singleWhere("id = " + id);
		}

		private Ticket singleWhere(String wClause)
		{
			Ticket ticket = null;

			string query = "SELECT * FROM Tickets WHERE " + wClause;
			connection.Open();
			SqlDataReader reader = connection.Read(query);
			if (reader.Read())
			{
				ticket = new Ticket();
				ticket.id = reader.GetInt32(0);
				ticket.schedule = scheduleDB.getScheduleById(reader.GetInt32(1));
				ticket.seat = seatDB.getSeatById(reader.GetInt32(2));
			}

			reader.Close();
			connection.Close();

			return ticket;
		}

		public List<Ticket> getTicketsBySchedule(int scheduleId)
		{
			return where("scheduleId = " + scheduleId);
		}

		private List<Ticket> where(String wClause)
		{
			List<Ticket> tickets = new List<Ticket>();

			string query;
			if (!wClause.Equals(""))
				query = "SELECT * FROM Tickets WHERE " + wClause;
			else
				query = "SELECT * FROM Tickets";
			connection.Open();
			SqlDataReader reader = connection.Read(query);

			while (reader.Read())
			{
				Ticket ticket = new Ticket();
				ticket.id = reader.GetInt32(0);
				ticket.schedule = scheduleDB.
				getScheduleById(reader.GetInt32(1));
				ticket.seat = seatDB.getSeatById(reader.GetInt32(2));
				tickets.Add(ticket);
			}

			reader.Close();
			connection.Close();

			return tickets;
		}

		public bool removeTicket(int id)
		{
			string query = "DELETE FROM Tickets WHERE id=" + id;
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			return result == 1;
		}

		public int insertTicket(Ticket ticket)
		{
			int id = connection.GetMaxId("Tickets") + 1;
			string query = @"INSERT INTO Tickets (id, ScheduleID, "+
				"SeatID) VALUES ( " + id + ", " + ticket.schedule.id + ", " 
				+ ticket.seat.id + ")";
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			
			if (result == 1)
				return id;
			return -1;
		}

		public bool updateTicket(Ticket ticket)
		{
			string query = @"UPDATE Tickets SET ScheduleID=" +
				ticket.schedule.id + ", SeatID=" + ticket.seat.id + 
				" WHERE id=" + ticket.id;
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			return result == 1;
		}
	}
}
